#include "database_sqlite.h"
#include "sqlite3.h"

#include <iostream>
#include <sstream>

namespace verema
{

Database_Sqlite::Database_Sqlite(std::string dbname_)
: _isOpen(false)
{
	connect(dbname_);
}

Database_Sqlite::~Database_Sqlite()
{
	disconnect();
}

void Database_Sqlite::connect(std::string dbname_)
{
	int rc;

	if( dbname_.size() > 0 )
	{
		rc = sqlite3_open(dbname_.c_str(), &_db);
		if( rc == SQLITE_OK )
		{
			_isOpen = true;
		}
		else
		{
			std::cout<< "ERROR:" << std::endl;
			std::cout<< "rc: " << rc << std::endl;
			std::cout<< "dbname: '" << dbname_ << "'" << std::endl;
			std::cout<< "Can't open database: " << sqlite3_errmsg(_db) << std::endl;
		}
	}
	else
	{
		std::cout<< "ERROR: Database_Sqlite" << std::endl;
		std::cout<< "ERROR: Database could not be opened: No Filename!" << std::endl;
	}
}

void Database_Sqlite::createNewDatabase(std::string newDbName)
{
    if( newDbName.size() > 0 )
    {
        if( _isOpen )
        {
            disconnect();
        }

        int rc;
		rc = sqlite3_open(newDbName.c_str(), &_db);
		if( rc == SQLITE_OK )
		{
			_isOpen = true;

            int rc_unit, rc_subunit, rc_item, rc_image, rc_config;
            sqlite3_stmt *ppStmt_unit, *ppStmt_subunit, *ppStmt_item, *ppStmt_image, *ppStmt_config;
            const char *pzTail;

            std::string createUnitTable =
                "create table units \
                ( \
                    id INTEGER PRIMARY KEY AUTOINCREMENT, \
                    name TEXT, \
                    description TEXT\
                );";

            std::string createSubunitTable =
                "create table subunits \
                ( \
                    id INTEGER PRIMARY KEY AUTOINCREMENT, \
                    name TEXT, \
                    description TEXT, \
                    unit_id INTEGER NOT NULL, \
                    image_id INTEGER \
                );";

            std::string createImageTable =
                "create table images \
                ( \
                    id INTEGER PRIMARY KEY AUTOINCREMENT, \
                    name TEXT, \
                    description TEXT, \
                    filepath TEXT, \
                    filename TEXT \
                );";

            std::string createItemTable =
                "create table items \
                ( \
                    id INTEGER PRIMARY KEY AUTOINCREMENT, \
                    name TEXT, \
                    description TEXT, \
                    subunit_id INTEGER NOT NULL, \
                    position TEXT, \
                    used INTEGER, \
                    property_01 TEXT, \
                    property_02 TEXT, \
                    property_03 TEXT, \
                    property_04 TEXT, \
                    property_05 TEXT, \
                    property_06 TEXT, \
                    property_07 TEXT, \
                    property_08 TEXT, \
                    property_09 TEXT, \
                    property_10 TEXT  \
                );";

            std::string createConfigTable =
                "create table config \
                ( \
                    name TEXT PRIMARY KEY, \
                    value TEXT \
                )";

            rc_unit    = sqlite3_prepare(_db, createUnitTable.c_str(), -1, &ppStmt_unit, &pzTail);
            if( rc_unit != SQLITE_OK )
            {
                std::cout<< "SQL: " << createUnitTable << std::endl;
                std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
            }
            else
            {
                rc = sqlite3_step(ppStmt_unit);
                if( rc == SQLITE_DONE )
                {
                    rc = sqlite3_finalize(ppStmt_unit);
                }
                else
                {
                    //std::cout<< "SQL: " << createUnitTable << std::endl;
                    std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
                }
            }

            rc_subunit = sqlite3_prepare(_db, createSubunitTable.c_str(), -1, &ppStmt_subunit, &pzTail);
            if( rc_subunit != SQLITE_OK )
            {
                std::cout<< "SQL: " << createSubunitTable << std::endl;
                std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
            }
            else
            {
                rc = sqlite3_step(ppStmt_subunit);
                if( rc == SQLITE_DONE )
                {
                    rc = sqlite3_finalize(ppStmt_subunit);
                }
                else
                {
                    //std::cout<< "SQL: " << createSubunitTable << std::endl;
                    std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
                }
            }

            rc_item    = sqlite3_prepare(_db, createItemTable.c_str(), -1, &ppStmt_item, &pzTail);
            if( rc_item != SQLITE_OK )
            {
                std::cout<< "SQL: " << createItemTable << std::endl;
                std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
            }
            else
            {
                rc = sqlite3_step(ppStmt_item);
                if( rc == SQLITE_DONE )
                {
                    rc = sqlite3_finalize(ppStmt_item);
                }
                else
                {
                    //std::cout<< "SQL: " << createItemTable << std::endl;
                    std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
                }
            }

            rc_image   = sqlite3_prepare(_db, createImageTable.c_str(), -1, &ppStmt_image, &pzTail);
            if( rc_image != SQLITE_OK )
            {
                std::cout<< "SQL: " << createImageTable << std::endl;
                std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
            }
            else
            {
                rc = sqlite3_step(ppStmt_image);
                if( rc == SQLITE_DONE )
                {
                    rc = sqlite3_finalize(ppStmt_image);
                }
                else
                {
                    //std::cout<< "SQL: " << createImageTable << std::endl;
                    std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
                }
            }

            rc_config   = sqlite3_prepare(_db, createConfigTable.c_str(), -1, &ppStmt_config, &pzTail);
            if( rc_image != SQLITE_OK )
            {
                std::cout<< "SQL: " << createConfigTable << std::endl;
                std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
            }
            else
            {
                rc = sqlite3_step(ppStmt_config);
                if( rc == SQLITE_DONE )
                {
                    rc = sqlite3_finalize(ppStmt_config);
                }
                else
                {
                    //std::cout<< "SQL: " << createImageTable << std::endl;
                    std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
                }
            }

            char *zErrMsg = 0;
            int temp_rc = sqlite3_exec(
                _db,
                "INSERT INTO config (name, value) VALUES( 'unit', 'unit'); \
                INSERT INTO config (name, value) VALUES( 'subunit', 'subunit'); \
                INSERT INTO config (name, value) VALUES( 'item', 'item');",
                NULL,
                NULL,
                &zErrMsg);

            if( temp_rc != SQLITE_OK )
            {
                std::cout << "SQL error: " << zErrMsg << std::endl;
                sqlite3_free(zErrMsg);
            }
		}
		else
		{
			std::cout<< "rc: " << rc << std::endl;
			std::cout<< "dbname: '" << newDbName << "'" << std::endl;
			std::cout<< "Can't open database: " << sqlite3_errmsg(_db) << std::endl;
		}
    }
}

void Database_Sqlite::openNewDatabase(std::string dbname)
{
    if( dbname.size() > 0 )
    {
        disconnect();
        connect(dbname);
    }
}

int Database_Sqlite::putUnit(Unit &unit_)
{
	int retVal = -1;

	if( _isOpen )
	{
	    int rc;
	    std::stringstream stream (std::stringstream::in | std::stringstream::out);
	    std::string sqlstring;

	    sqlite3_stmt *ppStmt;
		const char *pzTail;

		if(unit_.id() == -1)
		{
			stream << "INSERT INTO units (name, description) values ('" << unit_.name() << "', '" << unit_.description() << "');";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				//rc = sqlite3_reset(ppStmt);
				retVal = sqlite3_last_insert_rowid(_db);
				rc = sqlite3_finalize(ppStmt);
				//std::cout << "reset: " << rc << std::endl;
			}
		}
		else
		{
			stream << "UPDATE units SET name = '" << unit_.name() << "', description = '" << unit_.description() << "' WHERE id = " << unit_.id() << ";";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				//rc = sqlite3_reset(ppStmt);
				retVal = unit_.id();
				rc = sqlite3_finalize(ppStmt);
				//std::cout << "reset: " << rc << std::endl;
			}
		}
	}

	return(retVal);
}

Unit Database_Sqlite::getUnit(int id)
{
	Unit _unit;

	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "SELECT * FROM units WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc == SQLITE_ROW )
		{
			_unit.id(sqlite3_column_int(ppStmt, 0));
			_unit.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
			_unit.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));

			rc = sqlite3_finalize(ppStmt);
		}
		else
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
	}

	return( _unit );
}

void Database_Sqlite::getAllUnits(std::vector<Unit> &unit_vector_)
{
	if( _isOpen )
	{
		int rc;

		std::string sqlstring = "SELECT * FROM units;";

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		unit_vector_.clear();

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		while( rc == SQLITE_ROW )
		{
		    Unit unit;
		    unit.id(sqlite3_column_int(ppStmt, 0));
		    unit.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
		    unit.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));

		    unit_vector_.push_back(unit);

			rc = sqlite3_step(ppStmt);
		}

		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::putAllUnits(std::vector<Unit> &unit_vector_)
{
	if( _isOpen )
	{

	    begin_transaction();

	    for(std::vector<verema::Unit>::iterator it = unit_vector_.begin(); it != unit_vector_.end(); ++it)
	    {
		    putUnit(*it);
	    }

		end_transaction();
	}
}

void Database_Sqlite::deleteUnit(int id)
{
	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "DELETE FROM units WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc != SQLITE_DONE )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
		else
		{
			std::vector<Subunit> subunit_vector;
			getAllSubunits(subunit_vector, id);

			for(std::vector<Subunit>::iterator it = subunit_vector.begin(); it != subunit_vector.end(); ++it)
			{
				deleteSubunit(it->id());
			}

			rc = sqlite3_finalize(ppStmt);
		}
	}
}

Subunit Database_Sqlite::getSubunit(int id)
{
	Subunit _subunit;

	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "SELECT * FROM subunits WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc == SQLITE_ROW )
		{
			_subunit.id(sqlite3_column_int(ppStmt, 0));
			_subunit.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
			_subunit.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));
			_subunit.unit_id(sqlite3_column_int(ppStmt, 3));
			_subunit.image_id(sqlite3_column_int(ppStmt, 4));

			rc = sqlite3_finalize(ppStmt);
		}
		else
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
	}

	return _subunit;
}

//void Database_Sqlite::putSubunit(Subunit &subunit_)
int Database_Sqlite::putSubunit(Subunit &subunit_)
{
	int retVal = -1;

	if( _isOpen )
	{
	    int rc;
	    std::stringstream stream (std::stringstream::in | std::stringstream::out);
	    std::string sqlstring;

		sqlite3_stmt *ppStmt;
	    const char *pzTail;

		if(subunit_.id() == -1)
		{
			stream << "INSERT INTO subunits (name, description, unit_id, image_id) values ('" << subunit_.name() << "', '" << subunit_.description() << "', '" << subunit_.unit_id() << "', '" << subunit_.image_id() << "');";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				//rc = sqlite3_reset(ppStmt);
				retVal = sqlite3_last_insert_rowid(_db);
				rc = sqlite3_finalize(ppStmt);
				//std::cout << "reset: " << rc << std::endl;
			}
		}
		else
		{
			stream << "UPDATE subunits SET name = '" << subunit_.name() << "', description = '" << subunit_.description() << "', unit_id = '" << subunit_.unit_id() << "', image_id = '" << subunit_.image_id() << "' WHERE id = " << subunit_.id() << ";";
			sqlstring = stream.str();

			sqlite3_stmt *ppStmt;
			const char *pzTail;

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				//rc = sqlite3_reset(ppStmt);
				retVal = subunit_.id();
				rc = sqlite3_finalize(ppStmt);
				//std::cout << "reset: " << rc << std::endl;
			}
		}
	}

	return(retVal);
}

void Database_Sqlite::getAllSubunits(std::vector<Subunit> &subunit_vector_, int unit_id_)
{
	if( _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream << "SELECT * FROM subunits WHERE unit_id = " << unit_id_ << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		subunit_vector_.clear();

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		while( rc == SQLITE_ROW )
		{
		    Subunit subunit;
		    subunit.id(sqlite3_column_int(ppStmt, 0));
		    subunit.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
		    subunit.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));
			subunit.unit_id(sqlite3_column_int(ppStmt, 3));
			subunit.image_id(sqlite3_column_int(ppStmt, 4));

		    subunit_vector_.push_back(subunit);

			rc = sqlite3_step(ppStmt);
		}

		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::putAllSubunits(std::vector<Subunit> &subunit_vector_)
{
	if( _isOpen )
	{
		begin_transaction();

	    for(std::vector<Subunit>::iterator it = subunit_vector_.begin(); it != subunit_vector_.end(); ++it)
	    {
            putSubunit(*it);
	    }

		end_transaction();
	}
}

void Database_Sqlite::deleteSubunit(int id)
{
	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "DELETE FROM subunits WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc != SQLITE_DONE )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
		else
		{
			std::stringstream stream2 (std::stringstream::in  | std::stringstream::out);
			stream2<< "DELETE FROM items WHERE subunit_id = " << id << ";";
			std::string sqlstring2 = stream2.str();

			sqlite3_stmt *ppStmt2;
			const char *pzTail2;

			rc = sqlite3_prepare(_db, sqlstring2.c_str(), -1, &ppStmt2, &pzTail2);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring2 << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt2);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring2 << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				//rc = sqlite3_reset(ppStmt);
				rc = sqlite3_finalize(ppStmt2);
				//std::cout << "reset: " << rc << std::endl;
			}
		}

		rc = sqlite3_finalize(ppStmt);
	}
}

Item Database_Sqlite::getItem(int id)
{
	Item _item;

	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "SELECT * FROM items WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc == SQLITE_ROW )
		{
			_item.id(sqlite3_column_int(ppStmt, 0));
			_item.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
			_item.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));
			_item.subunit_id(sqlite3_column_int(ppStmt, 3));

			positionStringToItem(_item, reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));
			//_item.position(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));
			_item.used(sqlite3_column_int(ppStmt, 5));

			rc = sqlite3_finalize(ppStmt);
		}
		else
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
	}

	return _item;
}

//void Database_Sqlite::putItem(Item &item_)
int Database_Sqlite::putItem(Item &item_)
{
	int retVal = -1;

	if( _isOpen )
	{
	    int rc;
	    std::stringstream stream (std::stringstream::in | std::stringstream::out);
	    std::string sqlstring;

	    std::string position = createPositionString(item_);

	    sqlite3_stmt *ppStmt;
		const char *pzTail;

		int item_used = 0;

		if( item_.used() )
		{
			item_used = 1;
		}

		if(item_.id() == -1)
		{
			stream << "INSERT INTO items (name, description, subunit_id, position, used) values ('" << item_.name() << "', '" << item_.description() << "', " << item_.subunit_id() << ", '" << position << "', " << item_used << ");";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				retVal = sqlite3_last_insert_rowid(_db);
				rc = sqlite3_finalize(ppStmt);
			}
		}
		else
		{
			stream << "UPDATE items SET name = '" << item_.name() << "', description = '" << item_.description() << "', subunit_id = " << item_.subunit_id() << ", position = '" << position << "', used = " << item_used << " WHERE id = " << item_.id() << ";";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				//rc = sqlite3_reset(ppStmt);
				retVal = item_.id();
				rc = sqlite3_finalize(ppStmt);
				//std::cout << "reset: " << rc << std::endl;
			}
		}
	}

	return(retVal);
}

void Database_Sqlite::putAllItems(std::vector<Item> &item_vector_)
{
	if( _isOpen )
	{
		begin_transaction();

	    for(std::vector<Item>::iterator it = item_vector_.begin(); it != item_vector_.end(); ++it)
	    {
	        putItem(*it);
	    }

		end_transaction();
	}
}

void Database_Sqlite::getAllItems(std::vector<Item> &item_vector_, int subunit_id_)
{
	if( _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream << "SELECT * FROM items WHERE subunit_id = " << subunit_id_ << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		item_vector_.clear();

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		while( rc == SQLITE_ROW )
		{
		    Item item;
		    item.id(sqlite3_column_int(ppStmt, 0));
		    item.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
		    item.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));
			item.subunit_id(sqlite3_column_int(ppStmt, 3));

			positionStringToItem(item, reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));
			//item.position(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));

			if( sqlite3_column_int(ppStmt, 5) == 0 )
			{
				item.used(false);
			}
			else
			{
				item.used(true);
			}

		    item_vector_.push_back(item);

			rc = sqlite3_step(ppStmt);
		}

		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::getAllItems(std::vector<Item> &item_vector_, int subunit_id_, bool used_)
{
	if( _isOpen )
	{
		int rc;
		int item_used = 0;

		if( used_ )
		{
			item_used = 1;
		}

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream << "SELECT * FROM items WHERE subunit_id = " << subunit_id_ << " AND used = " << item_used << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		item_vector_.clear();

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		while( rc == SQLITE_ROW )
		{
		    Item item;
		    item.id(sqlite3_column_int(ppStmt, 0));
		    item.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
		    item.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));
			item.subunit_id(sqlite3_column_int(ppStmt, 3));

			positionStringToItem(item, reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));
			//item.position(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));

			if( sqlite3_column_int(ppStmt, 5) == 0 )
			{
				item.used(false);
			}
			else
			{
				item.used(true);
			}

		    item_vector_.push_back(item);

			rc = sqlite3_step(ppStmt);
		}

		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::deleteItem(int id)
{
	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "DELETE FROM items WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc != SQLITE_DONE )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
		else
		{
			rc = sqlite3_finalize(ppStmt);
		}
	}
}

Image Database_Sqlite::getImage(int id)
{
	Image image;

	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "SELECT * FROM images WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc == SQLITE_ROW )
		{
			image.id(sqlite3_column_int(ppStmt, 0));
			image.name(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 1)));
			image.description(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 2)));
			image.filepath(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 3)));
			image.filename(reinterpret_cast<const char*>(sqlite3_column_text(ppStmt, 4)));

			rc = sqlite3_finalize(ppStmt);
		}
		else
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
	}

	return image;
}

//void Database_Sqlite::putImage(Image &image_)
int Database_Sqlite::putImage(Image &image_)
{
    int retVal = -1;

	if( _isOpen )
	{
	    int rc;
	    std::stringstream stream (std::stringstream::in | std::stringstream::out);
	    std::string sqlstring;

	    sqlite3_stmt *ppStmt;
		const char *pzTail;

		if(image_.id() == -1)
		{
			stream << "INSERT INTO images (name, description, filepath, filename) values ('" << image_.name() << "', '" << image_.description() << "', '" << image_.filepath() << "', '" << image_.filename() << "');";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
			    retVal = sqlite3_last_insert_rowid(_db);
				rc = sqlite3_finalize(ppStmt);
			}
		}
		else
		{
			stream << "UPDATE images SET name = '" << image_.name() << "', description = '" << image_.description() << "', filepath = '" << image_.filepath() << "', filename = '" << image_.filename() << "' WHERE id = " << image_.id() << ";";
			sqlstring = stream.str();

			rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
			    retVal = image_.id();
				rc = sqlite3_finalize(ppStmt);
			}
		}
	}

	return(retVal);
}

void Database_Sqlite::deleteImage(int id)
{
	if( id >= 0 && _isOpen )
	{
		int rc;

		std::stringstream stream (std::stringstream::in  | std::stringstream::out);
		stream<< "DELETE FROM images WHERE id = " << id << ";";
		std::string sqlstring = stream.str();

		sqlite3_stmt *ppStmt;
		const char *pzTail;

		rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
		if( rc != SQLITE_OK )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
		}

		rc = sqlite3_step(ppStmt);
		if( rc != SQLITE_DONE )
		{
		    std::cout<< "SQL: " << sqlstring << std::endl;
			std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
		}
		else
		{
			std::stringstream stream2 (std::stringstream::in  | std::stringstream::out);
			stream2<< "UPDATE subunits SET image_id = -1 WHERE image_id = " << id << ";";
			std::string sqlstring2 = stream2.str();

			sqlite3_stmt *ppStmt2;
			const char *pzTail2;

			rc = sqlite3_prepare(_db, sqlstring2.c_str(), -1, &ppStmt2, &pzTail2);
			if( rc != SQLITE_OK )
			{
			    std::cout<< "SQL: " << sqlstring2 << std::endl;
				std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
			}

			rc = sqlite3_step(ppStmt2);
			if( rc != SQLITE_DONE )
			{
			    std::cout<< "SQL: " << sqlstring2 << std::endl;
				std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
			}
			else
			{
				rc = sqlite3_finalize(ppStmt2);
			}
		}

		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::begin_transaction()
{
	int rc;
	std::string sqlstring = "BEGIN;";

	sqlite3_stmt *ppStmt;
	const char *pzTail;

	rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
	if( rc != SQLITE_OK )
	{
	    std::cout<< "SQL: " << sqlstring << std::endl;
		std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
	}

	rc = sqlite3_step(ppStmt);
	if( rc != SQLITE_DONE )
	{
	    std::cout<< "SQL: " << sqlstring << std::endl;
		std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
	}
	else
	{
		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::end_transaction()
{
	int rc;
	std::string sqlstring = "END;";

	sqlite3_stmt *ppStmt;
	const char *pzTail;

	rc = sqlite3_prepare(_db, sqlstring.c_str(), -1, &ppStmt, &pzTail);
	if( rc != SQLITE_OK )
	{
	    std::cout<< "SQL: " << sqlstring << std::endl;
		std::cout<< "Can't prepare statement: " << sqlite3_errmsg(_db) << std::endl;
	}

	rc = sqlite3_step(ppStmt);
	if( rc != SQLITE_DONE )
	{
	    std::cout<< "SQL: " << sqlstring << std::endl;
		std::cout<< "Can't step statement: " << sqlite3_errmsg(_db) << std::endl;
	}
	else
	{
		rc = sqlite3_finalize(ppStmt);
	}
}

void Database_Sqlite::disconnect()
{
	if( _isOpen )
	{
		sqlite3_close(_db);
	}
}

std::string Database_Sqlite::createPositionString(Item &item)
{
    std::stringstream ss( std::stringstream::in | std::stringstream::out );

	ss << item.box().point1().x() << "/" << item.box().point1().y() << ";"
	   << item.box().point2().x() << "/" << item.box().point2().y() << ";"
	   << item.box().point3().x() << "/" << item.box().point3().y() << ";"
	   << item.box().point4().x() << "/" << item.box().point4().y() << ";";

	return(ss.str());
}

void Database_Sqlite::positionStringToItem(Item &item, const std::string &positionString)
{
	int lastIndicator = -1;
	int x_value = -1;
	int y_value = -1;
	int point_counter = 1;

	for(unsigned int i = 0; i < positionString.size(); ++i)
	{
		// x value
		if( positionString.at(i) == '/' )
		{
			//std::cout << position_.substr(lastIndicator +1, i - lastIndicator - 1) << std::endl;
			x_value = atoi(positionString.substr(lastIndicator +1, i - lastIndicator - 1).c_str());
			lastIndicator = i;
		}

		// y value
		if( positionString.at(i) == ';' )
		{
			//std::cout << position_.substr(lastIndicator +1, i - lastIndicator - 1) << std::endl;
			y_value = atoi(positionString.substr(lastIndicator +1, i - lastIndicator - 1).c_str());
			lastIndicator = i;
		}

		if(x_value != -1 && y_value != -1)
		{
			switch(point_counter)
			{
				case 1:
					item.box().point1(x_value, y_value);
//					_box.point1().y(y_value);
					x_value = -1; y_value = -1;
					++point_counter;
					break;
				case 2:
					item.box().point2(x_value, y_value);
//					_box.point2().y(y_value);
					x_value = -1; y_value = -1;
					++point_counter;
					break;
				case 3:
					item.box().point3(x_value, y_value);
//					_box.point3().y(y_value);
					x_value = -1; y_value = -1;
					++point_counter;
					break;
				case 4:
					item.box().point4(x_value, y_value);
//					_box.point4().y(y_value);
					x_value = -1; y_value = -1;
					++point_counter;
					break;
			}
		}
	}
}

} // namespace verema
